const pool = require('../conf/db')

// 获取最新一条日报内容
const getLastDailyWork = async (query) => {
  const connection = await pool.getConnection()
  try {
    const { userId, date } = query
    const [rows] = await connection.query(
      'SELECT * FROM work WHERE userId = ? and date < ? order by id desc limit 1;',
      [userId, date]
    )
    return rows
  } catch (err) {
    throw new Error('获取日报内容失败')
  } finally {
    connection.release()
  }
}

// 获取日报内容
const getDailyWork = async (query) => {
  const connection = await pool.getConnection()
  try {
    const { userId, date } = query
    const [rows] = await connection.query('SELECT * FROM work WHERE userId = ? AND date = ?;', [
      userId,
      date
    ])
    return rows
  } catch (err) {
    throw new Error('获取日报内容失败')
  } finally {
    connection.release()
  }
}

// 获取日报内容--详细
const getDetailDailyWork = async (query) => {
  const connection = await pool.getConnection()
  try {
    const { userId, date } = query
    const [rows] = await connection.query(
      `
      SELECT w.*, u.name AS userName, u.date AS date, b.name AS businessName
      FROM work w
      JOIN users u ON w.userId = u.id
      JOIN business b ON w.businessId = b.id
      WHERE w.userId = ? AND w.date = ?;
      `,
      [userId, date]
    )
    return rows
  } catch (err) {
    throw new Error('获取日报内容失败')
  } finally {
    connection.release()
  }
}

// 获取摸排信息条目
const getMpxxItems = async (date) => {
  const connection = await pool.getConnection()

  try {
    const [rows] = await connection.query(
      `
      select distinct wl.infoType, wl.content, b.name AS businessName,b.id as businessId
      FROM worklist wl
      inner JOIN work w ON wl.workId = W.id
      inner JOIN business b ON w.businessId = b.id
      WHERE w.date = ? and  wl.workType = 1
      order by businessId desc;
    `,
      [date]
    )
    return rows
  } catch (err) {
    throw new Error('获取信息摸排日报条目失败')
  } finally {
    connection.release()
  }
}

// 获取关联日报条目
const getAllItemsByWorkId = async (workId) => {
  const connection = await pool.getConnection()
  try {
    const [rows] = await connection.query('SELECT * FROM worklist WHERE workId =?', [workId])
    return rows
  } catch (err) {
    throw new Error('获取关联日报条目失败')
  } finally {
    connection.release()
  }
}

// 获取日报条目
const getAllItemsByType = async (query, workType) => {
  const { startDate, endDate, userId } = query
  const connection = await pool.getConnection()

  try {
    const [rows] = await connection.query(
      `
      select distinct  wl.*,u.name AS userName, b.name AS businessName
      FROM worklist wl
      inner JOIN work w ON wl.workId = W.id 
      inner JOIN users u ON w.userId = u.id
      inner JOIN business b ON w.businessId = b.id
      WHERE w.date >= ? AND w.date <= ? and  wl.workType = ? and w.userId = ?;
    `,
      [startDate, endDate, workType, userId]
    )
    return rows
  } catch (err) {
    throw new Error('获取信息摸排日报条目失败')
  } finally {
    connection.release()
  }
}

// 删除关联日报条目
const delWorkListByWorkId = async (workId) => {
  const connection = await pool.getConnection()
  try {
    return await connection.execute(`DELETE FROM worklist WHERE workId = ?;`, [workId])
  } catch (err) {
    throw new Error('删除日报条目失败')
  } finally {
    connection.release()
  }
}

// 保存日报条目
const saveNewWork = async (params) => {
  const { userId, date, workContent, zcType, businessId } = params
  const lastTime = new Date().toLocaleString()
  const connection = await pool.getConnection()
  try {
    return await connection.execute(
      'INSERT INTO work (userId,date,lastTime,workContent,zcType,businessId ) VALUES (?,?,?,?,?,?);',
      [userId, date, lastTime, workContent || '', zcType || '', businessId || '']
    )
  } catch (err) {
    throw new Error('保存日报条目失败')
  } finally {
    connection.release()
  }
}

// 修改日报条目
const updateWork = async (params) => {
  const { id, workContent, zcType, businessId } = params
  const lastTime = new Date().toLocaleString()
  const connection = await pool.getConnection()
  try {
    return await connection.execute(
      `UPDATE work SET lastTime = ?, workContent = ?, zcType = ?, businessId = ? WHERE id = ?;`,
      [lastTime, workContent || '', zcType || '', businessId || '', id]
    )
  } catch (err) {
    throw new Error('更新日报条目失败' + JSON.stringify(err))
  } finally {
    connection.release()
  }
}
// 修改日报条目
const hasFinished = async (id) => {
  const connection = await pool.getConnection()
  try {
    return await connection.execute(`UPDATE work SET finished = 1 WHERE id = ?;`, [id])
  } catch (err) {
    throw new Error('更新日报条目失败' + JSON.stringify(err))
  } finally {
    connection.release()
  }
}
// 是否都已上传完毕
const allFinished = async (date) => {
  const connection = await pool.getConnection()
  try {
    const [rows] = await connection.query(
      `SELECT w.* ,u.name AS userName
      FROM work w
      inner JOIN users u ON w.userId = u.id
      WHERE w.finished = 1 and w.date = ?`,
      [date]
    )
    return rows.map((i) => i.userName)
  } catch (err) {
    throw new Error('查询失败')
  } finally {
    connection.release()
  }
}

// 保存关联日报条目
const saveWorkList = async (workList, workId) => {
  const connection = await pool.getConnection()
  try {
    const values = workList
      .map(
        (item) =>
          `(${workId}, ${item.workType}, '${item.content}', '${item.infoType || ''}', '${item.projectName || ''}', '${item.businessWorkType || ''}')`
      )
      .join(',')
    const sql = `INSERT INTO worklist (workId,workType,content,infoType,projectName,businessWorkType) VALUES ${values}`
    return await connection.execute(sql)
  } catch (err) {
    throw new Error('保存关联日报条目失败')
  } finally {
    connection.release()
  }
}

module.exports = {
  getLastDailyWork,
  getDailyWork,
  getDetailDailyWork,
  getAllItemsByWorkId,
  getAllItemsByType,
  saveWorkList,
  delWorkListByWorkId,
  saveNewWork,
  updateWork,
  getMpxxItems,
  hasFinished,
  allFinished
}
